#include "database.h"
#include <QApplication>
#include <QMessageBox>

Database::Database(QObject *parent)
    : QObject(parent)
{
    m_database = QSqlDatabase::addDatabase("QSQLITE");
}

Database::~Database()
{
    closeDatabase();
}

bool Database::initializeDatabase()
{
    QString dbPath = getDatabasePath();
    m_database.setDatabaseName(dbPath);
    
    if (!m_database.open()) {
        setLastError(QString("无法打开数据库: %1").arg(m_database.lastError().text()));
        return false;
    }
    
    // 创建表结构
    if (!createTables()) {
        return false;
    }
    
    // 检查是否需要插入示例数据
    QSqlQuery query(m_database);
    query.prepare("SELECT COUNT(*) FROM products");
    if (query.exec() && query.next()) {
        int count = query.value(0).toInt();
        if (count == 0) {
            insertSampleData();
        }
    }
    
    return true;
}

void Database::closeDatabase()
{
    if (m_database.isOpen()) {
        m_database.close();
    }
}

bool Database::isConnected() const
{
    return m_database.isOpen();
}

bool Database::createTables()
{
    QSqlQuery query(m_database);
    
    QString createTableSQL = R"(
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT NOT NULL,
            stock INTEGER NOT NULL DEFAULT 0,
            price REAL NOT NULL DEFAULT 0.0,
            status TEXT NOT NULL DEFAULT '在售',
            created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    )";
    
    if (!query.exec(createTableSQL)) {
        setLastError(QString("创建表失败: %1").arg(query.lastError().text()));
        return false;
    }
    
    // 创建触发器自动更新 updated_time
    QString triggerSQL = R"(
        CREATE TRIGGER IF NOT EXISTS update_products_timestamp 
        AFTER UPDATE ON products
        FOR EACH ROW
        BEGIN
            UPDATE products SET updated_time = CURRENT_TIMESTAMP WHERE id = NEW.id;
        END
    )";
    
    if (!query.exec(triggerSQL)) {
        setLastError(QString("创建触发器失败: %1").arg(query.lastError().text()));
        return false;
    }
    
    return true;
}

bool Database::insertSampleData()
{
    QSqlQuery query(m_database);
    
    // 准备插入语句
    QString insertSQL = R"(
        INSERT INTO products (name, category, stock, price, status, created_time, updated_time) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
    )";
    
    query.prepare(insertSQL);
    
    // 示例数据
    QList<Product> sampleProducts = {
        Product(1, "可乐（原味）", "碳酸饮料", 8, 3.00, "在售", 
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(2, "雪碧（柠檬味）", "碳酸饮料", 12, 3.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(3, "矿泉水（550ml）", "饮用水", 20, 2.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(4, "红牛（能量饮料）", "功能饮料", 5, 6.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(5, "橙汁（鲜榨风味）", "果汁饮品", 7, 4.50, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(6, "美式咖啡（常温）", "咖啡饮品", 9, 5.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-02 14:30:00", "yyyy-MM-dd hh:mm:ss")),
        Product(7, "茶派（蜜桃味）", "茶类饮品", 11, 4.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(8, "可乐（无糖）", "碳酸饮料", 6, 3.50, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-03 10:15:00", "yyyy-MM-dd hh:mm:ss")),
        Product(9, "芬达（橙味）", "碳酸饮料", 10, 3.00, "在售",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss")),
        Product(10, "营养快线（原味）", "乳制品", 4, 5.50, "售罄",
                QDateTime::fromString("2024-05-01 09:00:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-04 16:20:00", "yyyy-MM-dd hh:mm:ss")),
        Product(11, "冰红茶（柠檬味）", "茶类饮品", 15, 3.50, "在售",
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss")),
        Product(12, "旺仔牛奶（245ml）", "乳制品", 8, 4.00, "在售",
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss")),
        Product(13, "苏打水（青柠味）", "饮用水", 13, 3.00, "在售",
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-04 09:20:00", "yyyy-MM-dd hh:mm:ss")),
        Product(14, "芒果汁（浓缩风味）", "果汁饮品", 6, 4.80, "在售",
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-02 08:45:00", "yyyy-MM-dd hh:mm:ss")),
        Product(15, "拿铁咖啡（热）", "咖啡饮品", 0, 5.50, "下架",
                QDateTime::fromString("2024-05-03 11:20:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-05 11:30:00", "yyyy-MM-dd hh:mm:ss")),
        Product(16, "冰糖雪梨（罐装）", "茶类饮品", 14, 3.20, "在售",
                QDateTime::fromString("2024-05-03 11:20:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-03 11:20:00", "yyyy-MM-dd hh:mm:ss")),
        Product(17, "脉动（青柠味）", "功能饮料", 17, 3.80, "在售",
                QDateTime::fromString("2024-05-03 11:20:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-05 16:40:00", "yyyy-MM-dd hh:mm:ss")),
        Product(18, "椰汁（生榨风味）", "果汁饮品", 7, 5.00, "在售",
                QDateTime::fromString("2024-05-04 13:10:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-04 13:10:00", "yyyy-MM-dd hh:mm:ss")),
        Product(19, "可乐（樱桃味）", "碳酸饮料", 3, 3.50, "促销",
                QDateTime::fromString("2024-05-04 13:10:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-06 09:15:00", "yyyy-MM-dd hh:mm:ss")),
        Product(20, "菊花茶（清热型）", "茶类饮品", 12, 3.30, "在售",
                QDateTime::fromString("2024-05-05 10:05:00", "yyyy-MM-dd hh:mm:ss"),
                QDateTime::fromString("2024-05-05 10:05:00", "yyyy-MM-dd hh:mm:ss"))
    };
    
    // 批量插入数据
    for (const Product& product : sampleProducts) {
        query.addBindValue(product.name);
        query.addBindValue(product.category);
        query.addBindValue(product.stock);
        query.addBindValue(product.price);
        query.addBindValue(product.status);
        query.addBindValue(product.createdTime);
        query.addBindValue(product.updatedTime);
        
        if (!query.exec()) {
            setLastError(QString("插入数据失败: %1").arg(query.lastError().text()));
            return false;
        }
    }
    
    return true;
}

bool Database::addProduct(const Product& product)
{
    QSqlQuery query(m_database);
    
    QString insertSQL = R"(
        INSERT INTO products (name, category, stock, price, status, created_time, updated_time) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
    )";
    
    query.prepare(insertSQL);
    query.addBindValue(product.name);
    query.addBindValue(product.category);
    query.addBindValue(product.stock);
    query.addBindValue(product.price);
    query.addBindValue(product.status);
    
    QDateTime now = QDateTime::currentDateTime();
    query.addBindValue(now);
    query.addBindValue(now);
    
    if (!query.exec()) {
        setLastError(QString("添加商品失败: %1").arg(query.lastError().text()));
        return false;
    }
    
    return true;
}

bool Database::updateProduct(const Product& product)
{
    QSqlQuery query(m_database);
    
    QString updateSQL = R"(
        UPDATE products 
        SET name = ?, category = ?, stock = ?, price = ?, status = ?, updated_time = ?
        WHERE id = ?
    )";
    
    query.prepare(updateSQL);
    query.addBindValue(product.name);
    query.addBindValue(product.category);
    query.addBindValue(product.stock);
    query.addBindValue(product.price);
    query.addBindValue(product.status);
    query.addBindValue(QDateTime::currentDateTime());
    query.addBindValue(product.id);
    
    if (!query.exec()) {
        setLastError(QString("更新商品失败: %1").arg(query.lastError().text()));
        return false;
    }
    
    return true;
}

bool Database::deleteProduct(int id)
{
    QSqlQuery query(m_database);
    
    query.prepare("DELETE FROM products WHERE id = ?");
    query.addBindValue(id);
    
    if (!query.exec()) {
        setLastError(QString("删除商品失败: %1").arg(query.lastError().text()));
        return false;
    }
    
    return true;
}

QList<Product> Database::getAllProducts()
{
    QList<Product> products;
    QSqlQuery query(m_database);
    
    if (!query.exec("SELECT id, name, category, stock, price, status, created_time, updated_time FROM products ORDER BY id")) {
        setLastError(QString("查询商品失败: %1").arg(query.lastError().text()));
        return products;
    }
    
    while (query.next()) {
        Product product;
        product.id = query.value(0).toInt();
        product.name = query.value(1).toString();
        product.category = query.value(2).toString();
        product.stock = query.value(3).toInt();
        product.price = query.value(4).toDouble();
        product.status = query.value(5).toString();
        product.createdTime = query.value(6).toDateTime();
        product.updatedTime = query.value(7).toDateTime();
        
        products.append(product);
    }
    
    return products;
}

Product Database::getProductById(int id)
{
    Product product;
    QSqlQuery query(m_database);
    
    query.prepare("SELECT id, name, category, stock, price, status, created_time, updated_time FROM products WHERE id = ?");
    query.addBindValue(id);
    
    if (query.exec() && query.next()) {
        product.id = query.value(0).toInt();
        product.name = query.value(1).toString();
        product.category = query.value(2).toString();
        product.stock = query.value(3).toInt();
        product.price = query.value(4).toDouble();
        product.status = query.value(5).toString();
        product.createdTime = query.value(6).toDateTime();
        product.updatedTime = query.value(7).toDateTime();
    } else {
        setLastError(QString("查询商品失败: %1").arg(query.lastError().text()));
    }
    
    return product;
}

QString Database::getDatabasePath() const
{
    QString dataPath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QDir dir(dataPath);
    if (!dir.exists()) {
        dir.mkpath(dataPath);
    }
    return dataPath + "/smart_sell.db";
}

QString Database::getLastError() const
{
    return m_lastError;
}

void Database::setLastError(const QString& error)
{
    m_lastError = error;
    qDebug() << "Database Error:" << error;
}